﻿using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;

namespace DataLibrary.Utility
{
    public class ExcelHelper
    {

        //将excel转换为二维数组
        public static string[,] LoadExcel(string file)
        {

            IWorkbook workbook = new XSSFWorkbook(file);
            ISheet sheet = workbook.GetSheetAt(0);
            IRow cellNum = sheet.GetRow(0);
            int length = sheet.LastRowNum;
            int width = cellNum.LastCellNum;

            string[,] array = new string[length,width];

            for (int i = 0; i < length + 1 ; i++)
            {
                string item;
                try
                {
                    IRow row = sheet.GetRow(i + 1);//从第二行开始
                    if(row!= null)
                    {
                        for (int j = 0; j < width; j++)
                        {
                            if (row.Cells[j] != null)
                            {
                                item = row.Cells[j].ToString();
                                array[i, j] = item;
                            }
                        
                        }
                    }             
                }
                catch (Exception ex)
                {
                    LogHelper.Debug("LoadAccountSearchResultExcel:" + file, ex.ToString());
                    continue;
                }

            }
            workbook.Close();

            return array;
        }

        public static void ExortExcel<T>(List<T> accountList, string tableName)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("sheet");
            IRow Title = null;
            IRow rows = null;

            Type entityType = accountList[0].GetType();
            System.Reflection.PropertyInfo[] entityProperties = entityType.GetProperties();

            for (int i = 0; i <= accountList.Count; i++)
            {
                if (i == 0)
                {
                    Title = sheet.CreateRow(0);
                    for (int k = 1; k < entityProperties.Length + 1; k++)
                    {
                        Title.CreateCell(0).SetCellValue("序号");
                        Title.CreateCell(k).SetCellValue(entityProperties[k - 1].Name);
                    }

                    continue;
                }
                else
                {
                    rows = sheet.CreateRow(i);
                    object entity = accountList[i - 1];
                    for (int j = 1; j <= entityProperties.Length; j++)
                    {
                        object[] entityValues = new object[entityProperties.Length];
                        entityValues[j - 1] = entityProperties[j - 1].GetValue(entity,null);
                        rows.CreateCell(0).SetCellValue(i);
                        if (entityValues[j - 1] != null)
                        {
                            rows.CreateCell(j).SetCellValue(entityValues[j - 1].ToString());
                        }
                        else
                        {
                            rows.CreateCell(j).SetCellValue("");
                        }

                    }
                }
            }
            string fileName = Directory.GetCurrentDirectory() + "\\" + DateTime.Now.ToString("yyyyMMddHHmmss") + tableName + ".xlsx";

            using (FileStream ms = new FileStream(fileName, FileMode.OpenOrCreate))
            {
                workbook.Write(ms);
                ms.Close();
            }


        }
    }
}
